--
-- PostgreSQL database dump
--

-- Started on 2009-10-27 21:29:07 EDT

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1487 (class 1259 OID 16532)
-- Dependencies: 3
-- Name: nitrous_articles; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE nitrous_articles (
    article_id integer NOT NULL,
    title character varying NOT NULL,
    synopsis character varying,
    body character varying NOT NULL,
    user_id integer NOT NULL,
    date_published timestamp without time zone,
    fts_index tsvector,
    stat_active boolean
);


--
-- TOC entry 1495 (class 1259 OID 16588)
-- Dependencies: 3
-- Name: nitrous_articles_categories; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE nitrous_articles_categories (
    articles_categories_id integer NOT NULL,
    article_id integer NOT NULL,
    category_id integer NOT NULL
);


--
-- TOC entry 1493 (class 1259 OID 16570)
-- Dependencies: 3
-- Name: nitrous_articles_tags; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE nitrous_articles_tags (
    articles_tags_id integer NOT NULL,
    article_id integer NOT NULL,
    tag_id integer NOT NULL
);


--
-- TOC entry 1489 (class 1259 OID 16548)
-- Dependencies: 3
-- Name: nitrous_categories; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE nitrous_categories (
    category_id integer NOT NULL,
    name character varying,
    description character varying,
    stat_active boolean
);


--
-- TOC entry 1491 (class 1259 OID 16559)
-- Dependencies: 3
-- Name: nitrous_tags; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE nitrous_tags (
    tag_id integer NOT NULL,
    tag character varying NOT NULL
);


--
-- TOC entry 1485 (class 1259 OID 16519)
-- Dependencies: 1763 1764 3
-- Name: nitrous_users; Type: TABLE; Schema: public; Owner: -; Tablespace: 
--

CREATE TABLE nitrous_users (
    users_id integer NOT NULL,
    username character varying NOT NULL,
    password character varying NOT NULL,
    stat_admin boolean DEFAULT false NOT NULL,
    stat_active boolean DEFAULT true NOT NULL
);


--
-- TOC entry 20 (class 1255 OID 16609)
-- Dependencies: 312 3
-- Name: update_article_fts_index(); Type: FUNCTION; Schema: public; Owner: -
--

CREATE FUNCTION update_article_fts_index() RETURNS trigger
    AS $$BEGIN
	-- Update the fts_index
	NEW.fts_index =
		setweight( to_tsvector( coalesce( (SELECT array_to_string(array(SELECT t.tag FROM nitrous_articles_tags at JOIN nitrous_tags t ON t.tag_id = at.tag_id WHERE at.article_id = NEW.article_id), ' ')) ,'')), 'A' ) || ' ' ||
		setweight( to_tsvector( coalesce(NEW.title,'')), 'B' ) || ' ' ||
		setweight( to_tsvector( coalesce(NEW.synopsis,'')), 'D' ) || ' ' ||
		setweight( to_tsvector( coalesce(NEW.body,'')),'D');

	-- Return the new row
	RETURN NEW;
END$$
    LANGUAGE plpgsql;


--
-- TOC entry 1486 (class 1259 OID 16530)
-- Dependencies: 1487 3
-- Name: nitrous_articles_article_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE nitrous_articles_article_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 1792 (class 0 OID 0)
-- Dependencies: 1486
-- Name: nitrous_articles_article_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE nitrous_articles_article_id_seq OWNED BY nitrous_articles.article_id;


--
-- TOC entry 1494 (class 1259 OID 16586)
-- Dependencies: 3 1495
-- Name: nitrous_articles_categories_articles_categories_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE nitrous_articles_categories_articles_categories_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 1793 (class 0 OID 0)
-- Dependencies: 1494
-- Name: nitrous_articles_categories_articles_categories_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE nitrous_articles_categories_articles_categories_id_seq OWNED BY nitrous_articles_categories.articles_categories_id;


--
-- TOC entry 1492 (class 1259 OID 16568)
-- Dependencies: 3 1493
-- Name: nitrous_articles_tags_articles_tags_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE nitrous_articles_tags_articles_tags_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 1794 (class 0 OID 0)
-- Dependencies: 1492
-- Name: nitrous_articles_tags_articles_tags_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE nitrous_articles_tags_articles_tags_id_seq OWNED BY nitrous_articles_tags.articles_tags_id;


--
-- TOC entry 1488 (class 1259 OID 16546)
-- Dependencies: 1489 3
-- Name: nitrous_categories_category_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE nitrous_categories_category_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 1795 (class 0 OID 0)
-- Dependencies: 1488
-- Name: nitrous_categories_category_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE nitrous_categories_category_id_seq OWNED BY nitrous_categories.category_id;


--
-- TOC entry 1490 (class 1259 OID 16557)
-- Dependencies: 1491 3
-- Name: nitrous_tags_tag_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE nitrous_tags_tag_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 1796 (class 0 OID 0)
-- Dependencies: 1490
-- Name: nitrous_tags_tag_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE nitrous_tags_tag_id_seq OWNED BY nitrous_tags.tag_id;


--
-- TOC entry 1484 (class 1259 OID 16517)
-- Dependencies: 1485 3
-- Name: nitrous_users_users_id_seq; Type: SEQUENCE; Schema: public; Owner: -
--

CREATE SEQUENCE nitrous_users_users_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;


--
-- TOC entry 1797 (class 0 OID 0)
-- Dependencies: 1484
-- Name: nitrous_users_users_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
--

ALTER SEQUENCE nitrous_users_users_id_seq OWNED BY nitrous_users.users_id;


--
-- TOC entry 1765 (class 2604 OID 16535)
-- Dependencies: 1487 1486 1487
-- Name: article_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE nitrous_articles ALTER COLUMN article_id SET DEFAULT nextval('nitrous_articles_article_id_seq'::regclass);


--
-- TOC entry 1769 (class 2604 OID 16591)
-- Dependencies: 1494 1495 1495
-- Name: articles_categories_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE nitrous_articles_categories ALTER COLUMN articles_categories_id SET DEFAULT nextval('nitrous_articles_categories_articles_categories_id_seq'::regclass);


--
-- TOC entry 1768 (class 2604 OID 16573)
-- Dependencies: 1492 1493 1493
-- Name: articles_tags_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE nitrous_articles_tags ALTER COLUMN articles_tags_id SET DEFAULT nextval('nitrous_articles_tags_articles_tags_id_seq'::regclass);


--
-- TOC entry 1766 (class 2604 OID 16551)
-- Dependencies: 1489 1488 1489
-- Name: category_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE nitrous_categories ALTER COLUMN category_id SET DEFAULT nextval('nitrous_categories_category_id_seq'::regclass);


--
-- TOC entry 1767 (class 2604 OID 16562)
-- Dependencies: 1490 1491 1491
-- Name: tag_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE nitrous_tags ALTER COLUMN tag_id SET DEFAULT nextval('nitrous_tags_tag_id_seq'::regclass);


--
-- TOC entry 1762 (class 2604 OID 16522)
-- Dependencies: 1485 1484 1485
-- Name: users_id; Type: DEFAULT; Schema: public; Owner: -
--

ALTER TABLE nitrous_users ALTER COLUMN users_id SET DEFAULT nextval('nitrous_users_users_id_seq'::regclass);


--
-- TOC entry 1781 (class 2606 OID 16593)
-- Dependencies: 1495 1495
-- Name: nitrous_articles_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY nitrous_articles_categories
    ADD CONSTRAINT nitrous_articles_categories_pkey PRIMARY KEY (articles_categories_id);


--
-- TOC entry 1773 (class 2606 OID 16540)
-- Dependencies: 1487 1487
-- Name: nitrous_articles_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY nitrous_articles
    ADD CONSTRAINT nitrous_articles_pkey PRIMARY KEY (article_id);


--
-- TOC entry 1779 (class 2606 OID 16575)
-- Dependencies: 1493 1493
-- Name: nitrous_articles_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY nitrous_articles_tags
    ADD CONSTRAINT nitrous_articles_tags_pkey PRIMARY KEY (articles_tags_id);


--
-- TOC entry 1775 (class 2606 OID 16556)
-- Dependencies: 1489 1489
-- Name: nitrous_categories_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY nitrous_categories
    ADD CONSTRAINT nitrous_categories_pkey PRIMARY KEY (category_id);


--
-- TOC entry 1777 (class 2606 OID 16567)
-- Dependencies: 1491 1491
-- Name: nitrous_tags_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY nitrous_tags
    ADD CONSTRAINT nitrous_tags_pkey PRIMARY KEY (tag_id);


--
-- TOC entry 1771 (class 2606 OID 16529)
-- Dependencies: 1485 1485
-- Name: nitrous_users_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: 
--

ALTER TABLE ONLY nitrous_users
    ADD CONSTRAINT nitrous_users_pkey PRIMARY KEY (users_id);


--
-- TOC entry 1787 (class 2620 OID 16610)
-- Dependencies: 1487 20
-- Name: update_fts_index; Type: TRIGGER; Schema: public; Owner: -
--

CREATE TRIGGER update_fts_index
    BEFORE INSERT OR UPDATE ON nitrous_articles
    FOR EACH ROW
    EXECUTE PROCEDURE update_article_fts_index();


--
-- TOC entry 1785 (class 2606 OID 16594)
-- Dependencies: 1772 1487 1495
-- Name: nitrous_articles_categories_article_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY nitrous_articles_categories
    ADD CONSTRAINT nitrous_articles_categories_article_id_fkey FOREIGN KEY (article_id) REFERENCES nitrous_articles(article_id);


--
-- TOC entry 1786 (class 2606 OID 16599)
-- Dependencies: 1774 1489 1495
-- Name: nitrous_articles_categories_category_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY nitrous_articles_categories
    ADD CONSTRAINT nitrous_articles_categories_category_id_fkey FOREIGN KEY (category_id) REFERENCES nitrous_categories(category_id);


--
-- TOC entry 1783 (class 2606 OID 16576)
-- Dependencies: 1493 1487 1772
-- Name: nitrous_articles_tags_article_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY nitrous_articles_tags
    ADD CONSTRAINT nitrous_articles_tags_article_id_fkey FOREIGN KEY (article_id) REFERENCES nitrous_articles(article_id);


--
-- TOC entry 1784 (class 2606 OID 16581)
-- Dependencies: 1776 1491 1493
-- Name: nitrous_articles_tags_tag_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY nitrous_articles_tags
    ADD CONSTRAINT nitrous_articles_tags_tag_id_fkey FOREIGN KEY (tag_id) REFERENCES nitrous_tags(tag_id);


--
-- TOC entry 1782 (class 2606 OID 16541)
-- Dependencies: 1487 1770 1485
-- Name: nitrous_articles_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
--

ALTER TABLE ONLY nitrous_articles
    ADD CONSTRAINT nitrous_articles_user_id_fkey FOREIGN KEY (user_id) REFERENCES nitrous_users(users_id);


--
-- TOC entry 1791 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: -
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2009-10-27 21:29:12 EDT

--
-- PostgreSQL database dump complete
--

